<?php

class m_penilaian extends CI_Model {

    function __construct() {
        // Call the Model constructor
        parent::__construct();
    }

    // get last inserted id
    function get_last_inserted_id() {
        return $this->db->insert_id();
    }

    // get total dosen
    function get_total_dosen($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM
                (
                    SELECT a.* FROM users a 
                    JOIN com_role_user b ON a.user_id = b.user_id 
                    JOIN com_role c ON b.role_id = c.role_id 
                    LEFT JOIN penilaian_dosen d ON a.user_id = d.dosen_kd
                    WHERE c.role_id = 4 AND d.tahun = ?  
                    AND d.semester = ? AND a.nama_lengkap LIKE ?  
                    GROUP BY a.user_id
                )r";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return array();
        }
    }

    // get data dosen
    function get_data_dosen($params) {
        $sql = "SELECT a.* FROM users a 
                JOIN com_role_user b ON a.user_id = b.user_id 
                JOIN com_role c ON b.role_id = c.role_id 
                LEFT JOIN penilaian_dosen d ON a.user_id = d.dosen_kd
                WHERE c.role_id = 4 AND d.tahun = ?  
                AND d.semester = ? AND a.nama_lengkap LIKE ? 
                GROUP BY a.user_id 
                ORDER BY a.nama_lengkap ASC 
                LIMIT ?, ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }

    // get total input penilaian
    function get_total_input_penilaian($params) {
        $sql = "SELECT COUNT(*) AS 'total' FROM penilaian_dosen 
                WHERE tahun = ? AND semester = ? 
                AND dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total'];
        } else {
            return 0;
        }
    }

    // get total point penilaian
    function get_total_point_penilaian($params) {
        $sql = "SELECT SUM(b.penilaian_value) AS 'total_point' 
                FROM penilaian_dosen a 
                JOIN penilaian_dosen_detail b ON a.input_kd = b.input_kd 
                WHERE a.tahun = ? AND a.semester = ? 
                AND a.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['total_point'];
        } else {
            return 0;
        }
    }

    // get indeks performa dosen
    function get_indeks_performa_dosen($params){
        $sql = "SELECT ROUND((SUM(b.penilaian_value)/COUNT(*)), 2) AS 'indeks'
                FROM penilaian_dosen a 
                JOIN penilaian_dosen_detail b ON a.input_kd = b.input_kd 
                WHERE a.tahun = ? AND a.semester = ? 
                AND a.dosen_kd = ?";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result['indeks'];
        } else {
            return 0;
        }
    }


    // get total max input per mahasiswa (asumsi point tertinggi 4)
    function get_total_max_point_per_mhs($params) {
        $max_pt = $params;
        $sql = "SELECT COUNT(*) AS 'total' FROM instrumen_penilaian";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            $max_pt = $max_pt * $result['total'];
            return $max_pt;
        } else {
            return 0;
        }
    }

}